package com.itqf.dao.impl;

import com.itqf.dao.UserDao;
import com.itqf.entity.User;
import com.itqf.utils.Constants;
import com.itqf.utils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;


import java.sql.SQLException;
import java.util.List;

/**
 * 数据库访问实现类
 */
public class UserDaoImpl implements UserDao {

    //调用Apache工具类
    private static QueryRunner queryRunner = new QueryRunner();

    /**
     * 根据用户名查询数据
     *
     * @param username 查询的条件
     * @return
     * @throws SQLException
     */
    @Override
    public User selectUserByUname(String username) throws SQLException {
        String sql = "select u_id as uid , u_name as username , u_password as upassword" +
                ", u_sex as usex , u_status as ustatus , u_code as code , u_email as email " +
                ", u_role as urole from user where u_name = ?";
        return queryRunner.query(DbUtils.getConnection(), sql, new BeanHandler<User>(User.class), username);

    }

    @Override
    public int insert(User user) throws SQLException {
        String sql = "insert into user (u_name,u_password,u_sex,u_status," +
                "u_code,u_email,u_role) value (?,?,?,?,?,?,?)";
        Object[] obj = {user.getUsername(), user.getUpassword(), user.getUsex(),
                user.getUstatus(), user.getCode(), user.getEmail(), user.getUrole()};
        return queryRunner.update(DbUtils.getConnection(), sql, obj);
    }

    @Override
    public User selectUserByActiveCode(String code) throws SQLException {
        String sql = "select u_id as uid , u_name as username , u_password as upassword" +
                ", u_sex as usex , u_status as ustatus , u_code as code , u_email as email " +
                ", u_role as urole from user where u_code = ?";

        return queryRunner.query(DbUtils.getConnection(), sql, new BeanHandler<User>(User.class), code);
    }

    @Override
    public int updateStatusByUid(int u_id) throws SQLException {
        //编写sql语句
        String sql = "update user set u_status=? where u_id=?";
        //参数列表
        Object[] obj = {Constants.ACTIVE_SUCCESS,u_id};
        return queryRunner.update(DbUtils.getConnection(),sql,obj);
    }

    @Override
    public User selectUser(String username, String sex) throws SQLException {

        //编写sql语句
        String sql = "select u_id as uid , u_name as username , u_password as upassword" +
                ", u_sex as usex , u_status as ustatus , u_code as code , u_email as email " +
                ", u_role as urole from user where u_name = ? and u_sex = ?";

        //调用工具类
        User query = queryRunner.query(DbUtils.getConnection(), sql, new BeanHandler<User>(User.class), username, sex);
        return query;
    }

    @Override
    public List<User> selectAllUser() throws SQLException {
        String sql = "select u_id as uid , u_name as username , u_password as upassword\n" +
                ", u_sex as usex , u_status as ustatus , u_code as code , u_email as email\n" +
                ", u_role as urole from user ";

        List<User> query = queryRunner.query(DbUtils.getConnection(), sql, new BeanListHandler<User>(User.class));
        return query;
    }

    @Override
    public int deleteUserByUid(int uid) throws SQLException {
        //编写sql语句
        String sql = "delete from user where u_id = ?";

        int update = queryRunner.update(DbUtils.getConnection(), sql, uid);

        return update ;
    }
}

